package com.mqtt.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mqtt.system.domain.IOTData;
import com.mqtt.system.domain.vo.IOTDataPage;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wang
 * @version 1.0
 * @data 2024/3/25 下午 7:57
 * @desc 描述信息
 */
@Mapper
public interface IOTDataMapper extends BaseMapper<IOTData> {
    @Select("SELECT * FROM iot_data WHERE id IN (SELECT SUBSTRING_INDEX( group_concat( id ORDER BY `create_time` DESC ), ',', 1 ) FROM iot_data GROUP BY properties )")
    List<IOTData> selectFirstRecordForEachGroupUsingNativeSql();
    // @Select("DELETE FROM iot_data WHERE id NOT IN ( SELECT id FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY properties ORDER BY create_time DESC) AS rn FROM iot_data ) AS RankedRecords WHERE rn <= 50);")
    // Long deleteOtherRecords();
    @Select("DELETE FROM iot_data\n" +
            "WHERE (id, DATE(create_time), properties) IN (\n" +
            "    SELECT id, DATE(create_time), properties\n" +
            "    FROM (\n" +
            "        SELECT\n" +
            "            id,\n" +
            "            DATE(create_time) as date_created,\n" +
            "            properties,\n" +
            "            ROW_NUMBER() OVER (PARTITION BY DATE(create_time), properties ORDER BY create_time DESC ) as rn\n" +
            "        FROM\n" +
            "            iot_data\n" +
            "    ) as ranked_data\n" +
            "    WHERE rn > 200\n" +
            ");")
    Long deleteOtherRecords();
    @Select("SELECT *\n" +
            "FROM (\n" +
            "    SELECT *,\n" +
            "           ROW_NUMBER() OVER(PARTITION BY properties ORDER BY create_time DESC) AS rn\n" +
            "    FROM iot_data\n" +
            ") AS subquery\n" +
            "WHERE rn BETWEEN (#{page} - 1) * #{pageSize} + 1 AND #{page} * #{pageSize}\n" +
            "ORDER BY properties, create_time DESC;")
    List<IOTDataPage> pageSelect(@Param("page") Long page , @Param("pageSize") Long pageSize);
}
